--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.

--This SQL is generated for the model defined stored procedure [film_in_stock]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[film_in_stock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[film_in_stock]
--MODELID: 60ee7363-3db1-4956-b2b6-80edf8421433
GO

CREATE PROCEDURE [dbo].[film_in_stock]
(
	@p_film_id int = null,
	@p_store_id int = null,
	@p_film_count int out 
)
AS


    READS SQL DATA
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count;
END
--MODELID,BODY: 60ee7363-3db1-4956-b2b6-80edf8421433
GO

--This SQL is generated for the model defined stored procedure [film_not_in_stock]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[film_not_in_stock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[film_not_in_stock]
--MODELID: 3a9172b2-73e0-4c2a-88ff-85b0708c4277
GO

CREATE PROCEDURE [dbo].[film_not_in_stock]
(
	@p_film_id int = null,
	@p_store_id int = null,
	@p_film_count int out 
)
AS


    READS SQL DATA
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count;
END
--MODELID,BODY: 3a9172b2-73e0-4c2a-88ff-85b0708c4277
GO

--This SQL is generated for the model defined stored procedure [rewards_report]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rewards_report]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rewards_report]
--MODELID: 0b6b7205-1aa8-43a9-95a6-43ba4b6ce04c
GO

CREATE PROCEDURE [dbo].[rewards_report]
(
	@min_monthly_purchases tinyint = null,
	@min_dollar_amount_purchased decimal = null,
	@count_rewardees int out 
)
AS


    READS SQL DATA
    COMMENT 'Provides a customizable report on best customers'
proc: BEGIN

    DECLARE last_month_start DATE;
    DECLARE last_month_end DATE;

    /* Some sanity checks... */
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

    /* Determine start and end time periods */
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

    /*
        Create a temporary storage area for
        Customer IDs.
    */
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    /*
        Find all customers meeting the
        monthly purchase requirements
    */
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id
    FROM payment AS p
    WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    /* Populate OUT parameter with count of found customers */
    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    /*
        Output ALL customer information of matching rewardees.
        Customize output as needed.
    */
    SELECT c.*
    FROM tmpCustomer AS t
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

    /* Clean up */
    DROP TABLE tmpCustomer;
END
--MODELID,BODY: 0b6b7205-1aa8-43a9-95a6-43ba4b6ce04c
GO

